﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Project.dalSql;
using System.Data.SqlClient;
using WISD.Common;
using WISD.DBUtility.DBOperate;

namespace WISD.CarFriend.BLL.OA
{
    public class MemberCardModule
    {
        #region MaticCode
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string MemberCardID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_CF_OA_MemberCard");
            strSql.Append(" where MemberCardID=@MemberCardID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberCardID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = MemberCardID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(WISD.CarFriend.Model.CFModel.T_CF_OA_MemberCard model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_CF_OA_MemberCard(");
            strSql.Append("MemberCardID,MemberID,MemberLevel,AdmissionDate,ExpirationDate,Pwd,ResidualAmount,State,Memo,Riviser,RiviseDate,Memo1,Memo2,Memo3,Memo4,Memo5,Memo6,Memo7,Memo8)");
            strSql.Append(" values (");
            strSql.Append("@MemberCardID,@MemberID,@MemberLevel,@AdmissionDate,@ExpirationDate,@Pwd,@ResidualAmount,@State,@Memo,@Riviser,@RiviseDate,@Memo1,@Memo2,@Memo3,@Memo4,@Memo5,@Memo6,@Memo7,@Memo8)");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberCardID", SqlDbType.NVarChar,50),
					new SqlParameter("@MemberID", SqlDbType.NVarChar,50),
					new SqlParameter("@MemberLevel", SqlDbType.Int,4),
					new SqlParameter("@AdmissionDate", SqlDbType.DateTime),
					new SqlParameter("@ExpirationDate", SqlDbType.DateTime),
					new SqlParameter("@Pwd", SqlDbType.NVarChar,50),
					new SqlParameter("@ResidualAmount", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@Memo", SqlDbType.NVarChar,500),
					new SqlParameter("@Riviser", SqlDbType.NVarChar,50),
					new SqlParameter("@RiviseDate", SqlDbType.DateTime),
					new SqlParameter("@Memo1", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo2", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo3", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo4", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo5", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo6", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo7", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo8", SqlDbType.NVarChar,100)};
            parameters[0].Value = model.MemberCardID;
            parameters[1].Value = model.MemberID;
            parameters[2].Value = model.MemberLevel;
            parameters[3].Value = model.AdmissionDate;
            parameters[4].Value = model.ExpirationDate;
            parameters[5].Value = model.Pwd;
            parameters[6].Value = model.ResidualAmount;
            parameters[7].Value = model.State;
            parameters[8].Value = model.Memo;
            parameters[9].Value = model.Riviser;
            parameters[10].Value = model.RiviseDate;
            parameters[11].Value = model.Memo1;
            parameters[12].Value = model.Memo2;
            parameters[13].Value = model.Memo3;
            parameters[14].Value = model.Memo4;
            parameters[15].Value = model.Memo5;
            parameters[16].Value = model.Memo6;
            parameters[17].Value = model.Memo7;
            parameters[18].Value = model.Memo8;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(WISD.CarFriend.Model.CFModel.T_CF_OA_MemberCard model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_CF_OA_MemberCard set ");
            strSql.Append("MemberID=@MemberID,");
            strSql.Append("MemberLevel=@MemberLevel,");
            strSql.Append("AdmissionDate=@AdmissionDate,");
            strSql.Append("ExpirationDate=@ExpirationDate,");
            strSql.Append("Pwd=@Pwd,");
            strSql.Append("ResidualAmount=@ResidualAmount,");
            strSql.Append("State=@State,");
            strSql.Append("Memo=@Memo,");
            strSql.Append("Riviser=@Riviser,");
            strSql.Append("RiviseDate=@RiviseDate,");
            strSql.Append("Memo1=@Memo1,");
            strSql.Append("Memo2=@Memo2,");
            strSql.Append("Memo3=@Memo3,");
            strSql.Append("Memo4=@Memo4,");
            strSql.Append("Memo5=@Memo5,");
            strSql.Append("Memo6=@Memo6,");
            strSql.Append("Memo7=@Memo7,");
            strSql.Append("Memo8=@Memo8");
            strSql.Append(" where MemberCardID=@MemberCardID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberID", SqlDbType.NVarChar,50),
					new SqlParameter("@MemberLevel", SqlDbType.Int,4),
					new SqlParameter("@AdmissionDate", SqlDbType.DateTime),
					new SqlParameter("@ExpirationDate", SqlDbType.DateTime),
					new SqlParameter("@Pwd", SqlDbType.NVarChar,50),
					new SqlParameter("@ResidualAmount", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@Memo", SqlDbType.NVarChar,500),
					new SqlParameter("@Riviser", SqlDbType.NVarChar,50),
					new SqlParameter("@RiviseDate", SqlDbType.DateTime),
					new SqlParameter("@Memo1", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo2", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo3", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo4", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo5", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo6", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo7", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo8", SqlDbType.NVarChar,100),
					new SqlParameter("@MemberCardID", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.MemberID;
            parameters[1].Value = model.MemberLevel;
            parameters[2].Value = model.AdmissionDate;
            parameters[3].Value = model.ExpirationDate;
            parameters[4].Value = model.Pwd;
            parameters[5].Value = model.ResidualAmount;
            parameters[6].Value = model.State;
            parameters[7].Value = model.Memo;
            parameters[8].Value = model.Riviser;
            parameters[9].Value = model.RiviseDate;
            parameters[10].Value = model.Memo1;
            parameters[11].Value = model.Memo2;
            parameters[12].Value = model.Memo3;
            parameters[13].Value = model.Memo4;
            parameters[14].Value = model.Memo5;
            parameters[15].Value = model.Memo6;
            parameters[16].Value = model.Memo7;
            parameters[17].Value = model.Memo8;
            parameters[18].Value = model.MemberCardID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string MemberCardID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_CF_OA_MemberCard ");
            strSql.Append(" where MemberCardID=@MemberCardID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberCardID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = MemberCardID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string MemberCardIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_CF_OA_MemberCard ");
            strSql.Append(" where MemberCardID in (" + MemberCardIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public WISD.CarFriend.Model.CFModel.T_CF_OA_MemberCard GetModel(string MemberCardID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 MemberCardID,MemberID,MemberLevel,AdmissionDate,ExpirationDate,Pwd,ResidualAmount,State,Memo,Riviser,RiviseDate,Memo1,Memo2,Memo3,Memo4,Memo5,Memo6,Memo7,Memo8 from T_CF_OA_MemberCard ");
            strSql.Append(" where MemberCardID=@MemberCardID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberCardID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = MemberCardID;

            WISD.CarFriend.Model.CFModel.T_CF_OA_MemberCard model = new WISD.CarFriend.Model.CFModel.T_CF_OA_MemberCard();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["MemberCardID"] != null && ds.Tables[0].Rows[0]["MemberCardID"].ToString() != "")
                {
                    model.MemberCardID = ds.Tables[0].Rows[0]["MemberCardID"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MemberID"] != null && ds.Tables[0].Rows[0]["MemberID"].ToString() != "")
                {
                    model.MemberID = ds.Tables[0].Rows[0]["MemberID"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MemberLevel"] != null && ds.Tables[0].Rows[0]["MemberLevel"].ToString() != "")
                {
                    model.MemberLevel = int.Parse(ds.Tables[0].Rows[0]["MemberLevel"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AdmissionDate"] != null && ds.Tables[0].Rows[0]["AdmissionDate"].ToString() != "")
                {
                    model.AdmissionDate = DateTime.Parse(ds.Tables[0].Rows[0]["AdmissionDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ExpirationDate"] != null && ds.Tables[0].Rows[0]["ExpirationDate"].ToString() != "")
                {
                    model.ExpirationDate = DateTime.Parse(ds.Tables[0].Rows[0]["ExpirationDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Pwd"] != null && ds.Tables[0].Rows[0]["Pwd"].ToString() != "")
                {
                    model.Pwd = ds.Tables[0].Rows[0]["Pwd"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ResidualAmount"] != null && ds.Tables[0].Rows[0]["ResidualAmount"].ToString() != "")
                {
                    model.ResidualAmount = int.Parse(ds.Tables[0].Rows[0]["ResidualAmount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["State"] != null && ds.Tables[0].Rows[0]["State"].ToString() != "")
                {
                    model.State = int.Parse(ds.Tables[0].Rows[0]["State"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Memo"] != null && ds.Tables[0].Rows[0]["Memo"].ToString() != "")
                {
                    model.Memo = ds.Tables[0].Rows[0]["Memo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Riviser"] != null && ds.Tables[0].Rows[0]["Riviser"].ToString() != "")
                {
                    model.Riviser = ds.Tables[0].Rows[0]["Riviser"].ToString();
                }
                if (ds.Tables[0].Rows[0]["RiviseDate"] != null && ds.Tables[0].Rows[0]["RiviseDate"].ToString() != "")
                {
                    model.RiviseDate = DateTime.Parse(ds.Tables[0].Rows[0]["RiviseDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Memo1"] != null && ds.Tables[0].Rows[0]["Memo1"].ToString() != "")
                {
                    model.Memo1 = ds.Tables[0].Rows[0]["Memo1"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Memo2"] != null && ds.Tables[0].Rows[0]["Memo2"].ToString() != "")
                {
                    model.Memo2 = ds.Tables[0].Rows[0]["Memo2"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Memo3"] != null && ds.Tables[0].Rows[0]["Memo3"].ToString() != "")
                {
                    model.Memo3 = ds.Tables[0].Rows[0]["Memo3"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Memo4"] != null && ds.Tables[0].Rows[0]["Memo4"].ToString() != "")
                {
                    model.Memo4 = ds.Tables[0].Rows[0]["Memo4"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Memo5"] != null && ds.Tables[0].Rows[0]["Memo5"].ToString() != "")
                {
                    model.Memo5 = ds.Tables[0].Rows[0]["Memo5"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Memo6"] != null && ds.Tables[0].Rows[0]["Memo6"].ToString() != "")
                {
                    model.Memo6 = ds.Tables[0].Rows[0]["Memo6"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Memo7"] != null && ds.Tables[0].Rows[0]["Memo7"].ToString() != "")
                {
                    model.Memo7 = ds.Tables[0].Rows[0]["Memo7"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Memo8"] != null && ds.Tables[0].Rows[0]["Memo8"].ToString() != "")
                {
                    model.Memo8 = ds.Tables[0].Rows[0]["Memo8"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select MemberCardID,MemberID,MemberLevel,AdmissionDate,ExpirationDate,Pwd,ResidualAmount,State,Memo,Riviser,RiviseDate,Memo1,Memo2,Memo3,Memo4,Memo5,Memo6,Memo7,Memo8 ");
            strSql.Append(" FROM T_CF_OA_MemberCard ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" MemberCardID,MemberID,MemberLevel,AdmissionDate,ExpirationDate,Pwd,ResidualAmount,State,Memo,Riviser,RiviseDate,Memo1,Memo2,Memo3,Memo4,Memo5,Memo6,Memo7,Memo8 ");
            strSql.Append(" FROM T_CF_OA_MemberCard ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM T_CF_OA_MemberCard ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.MemberCardID desc");
            }
            strSql.Append(")AS Row, T.*  from T_CF_OA_MemberCard T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion


        #region SelfCode
        /// <summary>
        ///  初始化页面和查询数据
        /// </summary>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="condition">搜索条件</param>
        /// <returns></returns>
        public static string GetData(int pageSize, int pageIndex, string condition)
        {
            StringBuilder tableName = new StringBuilder(); //从哪些表里面进行查询
            tableName.Append("  T_CF_OA_MemberCard AS a INNER JOIN ");
            tableName.Append("  T_CF_OA_Member AS b ON a.MemberID = b.MemberID LEFT OUTER JOIN ");
            tableName.Append("  T_CF_OA_MemberCardType AS c ON a.MemberLevel = c.Id  ");
            string order = " a.MemberCardID desc ";  //排序的字段
            StringBuilder fields = new StringBuilder(); //返回的列
            fields.Append(" a.MemberCardID main, a.MemberCardID ,b.MemberID,b.Name ")
                    .Append(",c.MemberCardType,a.AdmissionDate,a.ExpirationDate ,a.ResidualAmount")
                    .Append(",case a.State when 1 then '正常' when 2 then '挂失' when 3 then '冻结' when 4 then '未审核' else '' end State");
            StringBuilder where = new StringBuilder(); //查询的条件
            where.Append(" 1=1 ");
            if (condition != "")
            {
                string[] array = condition.Split('@');
                string MemberCardID = array[0];
                string MemberID = array[1];
                string Name = array[2];
                string MemberLevel = array[3];
                string State = array[4];

                if (MemberCardID.Trim() != "null")
                {
                    where.AppendFormat("  and a.MemberCardID=\'{0}\'   ", MemberCardID);
                }
                if (MemberID.Trim() != "null")
                {
                    where.AppendFormat("  and a.MemberID=\'{0}\'  ", MemberID);
                }
                if (Name.Trim() != "null")
                {
                    where.AppendFormat(" and b.Name like \'%{0}%\'  ", Name);
                }
                if (MemberLevel.Trim() != "null")
                {
                    where.AppendFormat("  and a.MemberLevel={0}  ", MemberLevel);
                }
                if (State.Trim() != "null")
                {
                    where.AppendFormat("  and a.State={0}  ", State);
                }
            }
            object[] objectArray = new object[] { tableName.ToString(), fields.ToString(), order, pageSize, pageIndex, where };
            DataTable dt = new DataTable();
            int recordCount = 0;
            dt = dalSqlHelper.ExecuteProcedure(objectArray, ref recordCount);
            if (dt.Rows.Count > 0)
            {
                // 添加“消费项目”列
                dt.Columns.Add("ConsumeItems");
                foreach (DataRow dr in dt.Rows)
                {
                    dr["ConsumeItems"] = "<a href='javascript:;' action='consume'>[消费项目]</a>";
                }

                return dalSqlHelper.DtToSON(dt) + "@" + recordCount.ToString();
            }
            else
            {
                return "@0";
            }
        }


        /// <summary>
        /// 查询指定主键对应的记录
        /// </summary>
        /// <param name="pk"></param>
        /// <returns></returns>
        public static string GetOne(string pk)
        {
            StringBuilder tableName = new StringBuilder(); //从哪些表里面进行查询
            tableName.Append(" T_CF_OA_Member a, T_CF_OA_MemberCard b ");
            string order = " b.MemberCardID desc ";  //排序的字段
            StringBuilder fields = new StringBuilder(); //返回的列
            fields.Append(" a.Name,a.MobileTel,a.VehicleNo,a.VehicleStyle,")
                .Append("b.MemberCardID,b.MemberID,b.MemberLevel,b.AdmissionDate,b.ExpirationDate,b.Pwd,b.ResidualAmount,b.State,")
                .Append("b.Memo,b.Riviser,b.RiviseDate,b.Memo1,b.Memo2,b.Memo3,b.Memo4,b.Memo5,b.Memo6,b.Memo7,b.Memo8 ");

            StringBuilder where = new StringBuilder(); //查询的条件
            where.AppendFormat(" a.MemberID=b.MemberID  and b.MemberCardID=\'{0}\' ", pk);
            object[] objectArray = new object[] { tableName.ToString(), fields.ToString(), order, 1, 1, where };
            DataTable dt = new DataTable();
            int recordCount = 0;
            dt = dalSqlHelper.ExecuteProcedure(objectArray, ref recordCount);
            if (dt.Rows.Count > 0)
            {
                return dalSqlHelper.DtToSON(dt);
            }
            else
            {
                return "";
            }
        }


        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="dictionary"></param>
        /// <returns></returns>
        public static string AddEntity(IDictionary<string, string> dictionary)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into T_CF_OA_MemberCard(");
            sql.Append("MemberCardID,MemberID,MemberLevel,AdmissionDate,ExpirationDate,Pwd,ResidualAmount,State,Memo,Riviser,RiviseDate,Memo1,Memo2,Memo3,Memo4,Memo5,Memo6,Memo7,Memo8)");
            sql.Append(" values (");
            sql.Append("@MemberCardID,@MemberID,@MemberLevel,@AdmissionDate,@ExpirationDate,@Pwd,@ResidualAmount,@State,@Memo,@Riviser,@RiviseDate,@Memo1,@Memo2,@Memo3,@Memo4,@Memo5,@Memo6,@Memo7,@Memo8)");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberCardID", SqlDbType.NVarChar,50),
					new SqlParameter("@MemberID", SqlDbType.NVarChar,50),
					new SqlParameter("@MemberLevel", SqlDbType.Int,4),
					new SqlParameter("@AdmissionDate", SqlDbType.DateTime),
					new SqlParameter("@ExpirationDate", SqlDbType.DateTime),
					new SqlParameter("@Pwd", SqlDbType.NVarChar,50),
					new SqlParameter("@ResidualAmount", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@Memo", SqlDbType.NVarChar,500),
					new SqlParameter("@Riviser", SqlDbType.NVarChar,50),
					new SqlParameter("@RiviseDate", SqlDbType.DateTime),
					new SqlParameter("@Memo1", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo2", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo3", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo4", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo5", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo6", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo7", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo8", SqlDbType.NVarChar,100)};
            parameters[0].Value = StringHandle.GetDictionaryValue<string>(dictionary, "MemberCardID");
            parameters[1].Value = StringHandle.GetDictionaryValue<string>(dictionary, "MemberID");
            parameters[2].Value = StringHandle.GetDictionaryValue<int>(dictionary, "MemberLevel");
            parameters[3].Value = StringHandle.GetDictionaryValue<DateTime>(dictionary, "AdmissionDate");
            parameters[4].Value = StringHandle.GetDictionaryValue<DateTime>(dictionary, "ExpirationDate");
            parameters[5].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Pwd");
            parameters[6].Value = StringHandle.GetDictionaryValue<int>(dictionary, "ResidualAmount");
            parameters[7].Value = StringHandle.GetDictionaryValue<int>(dictionary, "State");
            parameters[8].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo");
            parameters[9].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Reviser");
            parameters[10].Value = StringHandle.GetDictionaryValue<DateTime>(dictionary, "ReviseDate");
            parameters[11].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo1");
            parameters[12].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo2");
            parameters[13].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo3");
            parameters[14].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo4");
            parameters[15].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo5");
            parameters[16].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo6");
            parameters[17].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo7");
            parameters[18].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo8");

            SqlObject sqlObject = new SqlObject();
            sqlObject.Sql = sql.ToString();
            sqlObject.PramS = parameters;
            List<SqlObject> sqlObjectList = new List<SqlObject>();
            sqlObjectList.Add(sqlObject);

            // 新增会员卡消费项目
            string memberCardId = StringHandle.GetDictionaryValue<string>(dictionary, "MemberCardID");
            int memberCardTypeId = StringHandle.GetDictionaryValue<int>(dictionary, "MemberLevel");
            string reviser = StringHandle.GetDictionaryValue<string>(dictionary, "Reviser");
            DataTable dt = GetCardConsumeItem(memberCardTypeId).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    int consumeItemId = NullHelper.NullProcess<int>(dr["Id"], -1);
                    string consumeItem = NullHelper.NullProcess<string>(dr["ConsumeItem"], "");
                    int consumeCount = NullHelper.NullProcess<int>(dr["ConsumeCount"], -1);
                    string memo1 = NullHelper.NullProcess<string>(dr["Memo1"], "0");    //是否固定消费时间（0：不固定消费时间 1：固定消费时间）
                    sqlObjectList.Add(AddMemberConsumeItem(memberCardId, memberCardTypeId, consumeItemId, consumeItem, consumeCount, reviser, memo1));
                }
            }

            if (dalSqlHelper.ExecuteTransaction(sqlObjectList))
            {
                //修改会员的开卡状态为已开卡
                string memberId1 = StringHandle.GetDictionaryValue<string>(dictionary, "MemberID");
                if (memberId1 != null && memberId1.Trim() != "")
                {
                    MemberModule.UpdateCardState(memberId1, 1);
                }

                return "Success";
            }
            else
            {
                return "Failure";
            }
        }


        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="pk"></param>
        /// <returns></returns>
        public static string DeleteEntity(string pk)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from T_CF_OA_MemberCard ");
            sql.Append(" where MemberCardID=@MemberCardID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberCardID", SqlDbType.NVarChar,50)};
            parameters[0].Value = pk;

            SqlObject sqlObject = new SqlObject();
            sqlObject.Sql = sql.ToString();
            sqlObject.PramS = parameters;
            List<SqlObject> sqlObjectList = new List<SqlObject>();
            sqlObjectList.Add(sqlObject);

            if (dalSqlHelper.ExecuteTransaction(sqlObjectList))
            {
                return "Success";
            }
            else
            {
                return "Failure";
            }
        }


        /// <summary>
        /// 修改记录
        /// </summary>
        /// <param name="dictionary"></param>
        /// <returns></returns>
        public static string EditEntity(IDictionary<string, string> dictionary)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update T_CF_OA_MemberCard set ");
            sql.Append("MemberID=@MemberID,");
            sql.Append("MemberLevel=@MemberLevel,");
            sql.Append("AdmissionDate=@AdmissionDate,");
            sql.Append("ExpirationDate=@ExpirationDate,");
            sql.Append("Pwd=@Pwd,");
            sql.Append("ResidualAmount=@ResidualAmount,");
            sql.Append("State=@State,");
            sql.Append("Memo=@Memo,");
            sql.Append("Riviser=@Riviser,");
            sql.Append("RiviseDate=@RiviseDate,");
            sql.Append("Memo1=@Memo1,");
            sql.Append("Memo2=@Memo2,");
            sql.Append("Memo3=@Memo3,");
            sql.Append("Memo4=@Memo4,");
            sql.Append("Memo5=@Memo5,");
            sql.Append("Memo6=@Memo6,");
            sql.Append("Memo7=@Memo7,");
            sql.Append("Memo8=@Memo8");
            sql.Append(" where MemberCardID=@MemberCardID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberID", SqlDbType.NVarChar,50),
					new SqlParameter("@MemberLevel", SqlDbType.Int,4),
					new SqlParameter("@AdmissionDate", SqlDbType.DateTime),
					new SqlParameter("@ExpirationDate", SqlDbType.DateTime),
					new SqlParameter("@Pwd", SqlDbType.NVarChar,50),
					new SqlParameter("@ResidualAmount", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@Memo", SqlDbType.NVarChar,500),
					new SqlParameter("@Riviser", SqlDbType.NVarChar,50),
					new SqlParameter("@RiviseDate", SqlDbType.DateTime),
					new SqlParameter("@Memo1", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo2", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo3", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo4", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo5", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo6", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo7", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo8", SqlDbType.NVarChar,100),
					new SqlParameter("@MemberCardID", SqlDbType.NVarChar,50)};
            parameters[0].Value = StringHandle.GetDictionaryValue<string>(dictionary, "MemberID");
            parameters[1].Value = StringHandle.GetDictionaryValue<int>(dictionary, "MemberLevel");
            parameters[2].Value = StringHandle.GetDictionaryValue<DateTime>(dictionary, "AdmissionDate");
            parameters[3].Value = StringHandle.GetDictionaryValue<DateTime>(dictionary, "ExpirationDate");
            parameters[4].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Pwd");
            parameters[5].Value = StringHandle.GetDictionaryValue<int>(dictionary, "ResidualAmount");
            parameters[6].Value = StringHandle.GetDictionaryValue<int>(dictionary, "State");
            parameters[7].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo");
            parameters[8].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Riviser");
            parameters[9].Value = StringHandle.GetDictionaryValue<DateTime>(dictionary, "RiviseDate");
            parameters[10].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo1");
            parameters[11].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo2");
            parameters[12].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo3");
            parameters[13].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo4");
            parameters[14].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo5");
            parameters[15].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo6");
            parameters[16].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo7");
            parameters[17].Value = StringHandle.GetDictionaryValue<string>(dictionary, "Memo8");
            parameters[18].Value = StringHandle.GetDictionaryValue<string>(dictionary, "MemberCardID");

            SqlObject sqlObject = new SqlObject();
            sqlObject.Sql = sql.ToString();
            sqlObject.PramS = parameters;
            List<SqlObject> sqlObjectList = new List<SqlObject>();
            sqlObjectList.Add(sqlObject);

            if (dalSqlHelper.ExecuteTransaction(sqlObjectList))
            {
                return "Success";
            }
            else
            {
                return "Failure";
            }
        }


        /// <summary>
        /// 获取会员信息
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static string GetMemberData(int pageSize, int pageIndex, string condition)
        {
            StringBuilder tableName = new StringBuilder(); //从哪些表里面进行查询
            tableName.Append(" T_CF_OA_Member a ");
            string order = " a.MemberID desc ";  //排序的字段
            StringBuilder fields = new StringBuilder(); //返回的列
            fields.Append("  a.MemberID + '#' + a.Name + '#' + a.MobileTel + '#' + a.VehicleNo + '#' + a.VehicleStyle as main,a.MemberID,a.Name,a.Sex,a.MobileTel,a.VehicleNo,a.VehicleStyle")
                        .Append(",case a.IsCard when 0 then '未开卡' when 1  then '已开卡' else '' end IsCard");
            StringBuilder where = new StringBuilder(); //查询的条件
            where.Append(" 1=1 ");
            if (condition != "")
            {
                string[] array = condition.Split('@');
                string MemberID = array[0];
                string Name = array[1];
                string MobileTel = array[2];
                string VehicleNo = array[3];
                string IsCard = array[4];

                if (MemberID.Trim() != "null")
                {
                    where.AppendFormat("  and a.MemberID=\'{0}\'  ", MemberID);
                }
                if (Name.Trim() != "null")
                {
                    where.AppendFormat("  and a.Name like N\'%{0}%\' ", Name);
                }
                if (MobileTel.Trim() != "null")
                {
                    where.AppendFormat("  and a.MobileTel=\'{0}\'  ", MobileTel);
                }
                if (VehicleNo.Trim() != "null")
                {
                    where.AppendFormat("  and a.VehicleNo=\'{0}\'  ", VehicleNo);
                }
                if (IsCard.Trim() != "null")
                {
                    where.AppendFormat("  and a.IsCard={0}  ", IsCard);
                }
            }
            object[] objectArray = new object[] { tableName.ToString(), fields.ToString(), order, pageSize, pageIndex, where };
            DataTable dt = new DataTable();
            int recordCount = 0;
            dt = dalSqlHelper.ExecuteProcedure(objectArray, ref recordCount);
            if (dt.Rows.Count > 0)
            {
                return dalSqlHelper.DtToSON(dt) + "@" + recordCount.ToString();
            }
            else
            {
                return "@0";
            }
        }


        /// <summary>
        /// 加载会员卡类型
        /// </summary>
        /// <returns></returns>
        public static string LoadMemberCardType()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" SELECT DISTINCT a.Id, a.MemberCardType ");
            sql.Append(" FROM T_CF_OA_MemberCardType AS a ");
            sql.Append(" where 1=@one ");
            SqlParameter[] parameters = new SqlParameter[] { 
               new SqlParameter("@one",1)
            };

            SqlObject sqlObject = new SqlObject();
            sqlObject.Sql = sql.ToString();
            sqlObject.PramS = parameters;
            DataTable dt = dalSqlHelper.ExecuteSelect(sqlObject);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dalSqlHelper.DtToSON(dt);
            }
            else
            {
                return "";
            }
        }


        /// <summary>
        /// 获取会员消费项目列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static string GetMemberConsumeItem(int pageSize, int pageIndex, string condition)
        {
            StringBuilder tableName = new StringBuilder(); //从哪些表里面进行查询
            tableName.Append("  T_CF_OA_MemberConsumeItem AS a INNER JOIN ");
            tableName.Append("  T_CF_OA_MemberCard AS b ON a.MemberCardId = b.MemberCardID LEFT OUTER JOIN ");
            tableName.Append("  T_CF_OA_MemberCardType AS c ON a.MemberCardTypeId = c.Id ");
            string order = "  a.ConsumeItemId ";  //排序的字段
            StringBuilder fields = new StringBuilder(); //返回的列
            fields.Append(" a.Id AS main, a.MemberCardId, c.MemberCardType, a.ConsumeItem, a.ConsumeCount, a.RemainConsumeCount ");
            StringBuilder where = new StringBuilder(); //查询的条件
            where.Append(" 1=1 ");
            if (condition != "")
            {
                where.AppendFormat("  and a.MemberCardId = \'{0}\'  ", condition);
            }
            object[] objectArray = new object[] { tableName.ToString(), fields.ToString(), order, pageSize, pageIndex, where };
            DataTable dt = new DataTable();
            int recordCount = 0;
            dt = dalSqlHelper.ExecuteProcedure(objectArray, ref recordCount);
            if (dt.Rows.Count > 0)
            {
                return dalSqlHelper.DtToSON(dt) + "@" + recordCount.ToString();
            }
            else
            {
                return "@0";
            }
        }


        #region 内部方法
        /// <summary>
        /// 获得数据列表
        /// </summary>
        private static DataSet GetCardConsumeItem(int memberCardTypeId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,MemberCardTypeId,ConsumeItem,ConsumeCount,Creator,CreateDate,Reviser,ReviseDate,Memo,Memo1,Memo2,Memo3,Memo4,Memo5,Memo6,Memo7,Memo8 ");
            strSql.Append(" FROM T_CF_OA_CardConsumeItem ");
            strSql.Append(" where MemberCardTypeId=" + memberCardTypeId.ToString());
            return WISD.DBUtility.DBOperate.DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 新增会员消费项目
        /// </summary>
        /// <param name="memberCardId"></param>
        /// <param name="memberCardTypeId"></param>
        /// <param name="consumeItemId"></param>
        /// <param name="consumeItem"></param>
        /// <param name="consumeCount"></param>
        /// <returns></returns>
        private static SqlObject AddMemberConsumeItem(string memberCardId, int memberCardTypeId, int consumeItemId, string consumeItem, int consumeCount, string reviser, string memo1)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into T_CF_OA_MemberConsumeItem(");
            sql.Append("MemberCardId,MemberCardTypeId,ConsumeItemId,ConsumeItem,ConsumeCount,RemainConsumeCount,Creator,CreateDate,Reviser,ReviseDate,Memo,Memo1,Memo2,Memo3,Memo4,Memo5,Memo6,Memo7,Memo8)");
            sql.Append(" values (");
            sql.Append("@MemberCardId,@MemberCardTypeId,@ConsumeItemId,@ConsumeItem,@ConsumeCount,@RemainConsumeCount,@Creator,@CreateDate,@Reviser,@ReviseDate,@Memo,@Memo1,@Memo2,@Memo3,@Memo4,@Memo5,@Memo6,@Memo7,@Memo8)");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberCardId", SqlDbType.NVarChar,50),
					new SqlParameter("@MemberCardTypeId", SqlDbType.Int,4),
					new SqlParameter("@ConsumeItemId", SqlDbType.Int,4),
					new SqlParameter("@ConsumeItem", SqlDbType.NVarChar,100),
					new SqlParameter("@ConsumeCount", SqlDbType.Int,4),
					new SqlParameter("@RemainConsumeCount", SqlDbType.Int,4),
					new SqlParameter("@Creator", SqlDbType.NVarChar,50),
					new SqlParameter("@CreateDate", SqlDbType.DateTime),
					new SqlParameter("@Reviser", SqlDbType.NVarChar,50),
					new SqlParameter("@ReviseDate", SqlDbType.DateTime),
					new SqlParameter("@Memo", SqlDbType.NVarChar,500),
					new SqlParameter("@Memo1", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo2", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo3", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo4", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo5", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo6", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo7", SqlDbType.NVarChar,100),
					new SqlParameter("@Memo8", SqlDbType.NVarChar,100)};
            parameters[0].Value = memberCardId;
            parameters[1].Value = memberCardTypeId;
            parameters[2].Value = consumeItemId;
            parameters[3].Value = consumeItem;
            parameters[4].Value = consumeCount;
            parameters[5].Value = consumeCount;
            parameters[6].Value = reviser;
            parameters[7].Value = DateTime.Now;
            parameters[8].Value = reviser;
            parameters[9].Value = DateTime.Now;
            parameters[10].Value = "";
            parameters[11].Value = memo1;
            parameters[12].Value = "";
            parameters[13].Value = "";
            parameters[14].Value = "";
            parameters[15].Value = "";
            parameters[16].Value = "";
            parameters[17].Value = "";
            parameters[18].Value = "";

            SqlObject sqlObject = new SqlObject();
            sqlObject.Sql = sql.ToString();
            sqlObject.PramS = parameters;
            return sqlObject;
        }
        #endregion

        #endregion
    }
}

